#!/usr/bin/env python3
# -*- coding:utf-8 -*-
"""

仅该文件可以对网点数据库进行写入


利用高德地图api实现地址和经纬度的转换

"""

import requests
import xlrd
import pymysql


def geocode(adrs):
    req = requests.get(f'https://restapi.amap.com/v3/geocode/geo?address={adrs}&output=JSON&key'
                       '=6487568d7a414da8b2dd04105d09748b')

    request_json = req.json()
    longitude_and_latitude = request_json['geocodes'][0]['location'].split(',')
    longitude = longitude_and_latitude[0]
    latitude = longitude_and_latitude[1]

    return longitude, latitude


def get_lon_and_lat():
    data = xlrd.open_workbook('dsts.xlsx')
    table = data.sheet_by_index(0)

    destination_name_list_x = table.col(0)  # 返回由该列中所有的单元格对象组成的列表
    destination_name_list = []
    destination_longitude_list = []
    destination_latitude_list = []

    for i in range(0, len(destination_name_list_x)):
        destination_name_list.append(destination_name_list_x[i].value)

    for i in range(0, len(destination_name_list)):
        lon, lat = geocode(destination_name_list[i])
        destination_longitude_list.append(lon)
        destination_latitude_list.append(lat)

    return destination_name_list, destination_longitude_list, destination_latitude_list


if __name__ == '__main__':
    destination_name_list, destination_longitude_list, destination_latitude_list = get_lon_and_lat()
    database = pymysql.connect(host="localhost", user="root", password="zzh200086", db="dachuang")
    cursor = database.cursor()

    destination_weight_list = [8.824934894, 7.375587827, 8.623492053, 4.471793255, 2.352144673,
                               6.803964745, 8.609318416, 8.918722585, 1.714122873, 9.170470094,
                               8.360166861, 3.944794957, 2.386734317, 1.278249076, 6.212404481,
                               1.261608653, 8.307934651, 5.823108579, 3.223684224, 9.157723472,
                               3.426481623, 2.493208655, 8.919128299, 3.698778273, 8.065164784,
                               2.408542568, 1.644974593, 2.498521597, 9.441086659, 8.494463109,
                               7.704172883, 5.91777781, 4.715200802, 3.983033235, 0.469570351]

    for i in range(0, len(destination_name_list)):
        sql = f"insert ignore into destination (id, dst_name, longitude, latitude, weight) " \
              f"VALUES ({i+1}, '{destination_name_list[i]}', {destination_longitude_list[i]}, " \
              f"{destination_latitude_list[i]},{destination_weight_list[i]})"
        cursor.execute(sql)
        database.commit()
        print(i)
